Once we have created a table in database, now we can insert data in it, and the data store in the form of rows. To insert data in a table we use the SQL INSERT command.
INSERT Data Syntax
In SQL we have two possible methods to insert data in a table: In first way, when we insert data, we also specify the column name along with the column value.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
In this way if we do not mention a column name and its value then automatically that data cell will be stored as NULL. In another Method we do not specify the column name, just specify the values in the same order as the columns of the tables have been created.
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Here is we do not put the values in the same order as the columns then the SQL will throw an error.
Example
First check the table description in what order the data should be put: Query: Check the table detail:
DESC students;
Output
+--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(6) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | | age | int(4) | YES | | NULL | | | grades | varchar(2) | YES | | NULL | | | marks | int(4) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.08 sec)
This table contains 5 fields id, name, age, grades and marks, so when we insert data, we need to follow the order. Query: Insert data in a table, with column names:
INSERT INTO students(id, name, age, grades, marks) VALUES(1,'Luffy',16,'A',970); INSERT INTO students(id, name, age, grades, marks) VALUES(2,'Naruto',18,'A',960); INSERT INTO students(id, name, age, grades, marks) VALUES(3,'Zoro',20,'A',940);
Query: Insert data in a table, without mentioning the column name:
INSERT INTO students VALUES(4,"Sanji", 21, "B", 899); INSERT INTO students VALUES(5,"Nami", 17, "B", 896);
Note: If the column represents a string data type then we use the double or single quotation to represent the data and if the column represents the numeric data type then we simply write the digits.
Default Values
If we use the first way of inserting values where we specify the column name too, there we get an advantage of auto-filling and defaults values. If we do not mention a column name and its value then by default the SQL fill that cell with NULL value.
Example
Query
INSERT INTO students(id, name, marks) VALUES(6,'Robin',860);
Here we did not specify the column names age and grades, and we also did not pass the values for them, so here SQL treat those data set as NULL values. See the student Table data To see the table data we use the SELECT and FROM commands.
Example
Query: See the Table data that has been inserted by INSERT commands.
Select * From students;
Output
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 1 | Luffy | 16 | A | 970 | | 2 | Naruto | 18 | A | 960 | | 3 | Zoro | 20 | A | 940 | | 4 | Sanji | 21 | B | 899 | | 5 | Nami | 17 | B | 896 | | 6 | Robin | NULL | NULL | 860 | +------+--------+------+--------+-------+ 6 rows in set (0.00 sec)
Also, if you want to know how to insert structured data like XML into SQL tables, you can find the tutorial on Sonra.io .
Summary
- To insert data in the table we use the SQL INSERT INTO command.
- The insert command fills the table row by row.
- There are two ways to use the INSERT command, specify column name or do not specify column name.
- If we do not pass the column name and its value then the SQL fill that cell with NULL.
People are also reading: